from django.shortcuts import render, redirect, render_to_response,HttpResponse
import pymysql
from django.template import RequestContext
import os

def classes(request):


    # 创建连接
    conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='t1',charset='utf8')
    # 创建游标
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

    # 执行SQL，并返回收影响行数
    cursor.execute("select id,title from class")

# 111
    classlist = cursor.fetchall()

    # 关闭游标
    cursor.close()
    # 关闭连接
    conn.close()
    return render(request,'classes.html',{'class_list':classlist})
    # return render(request,'/classes/',{'class_list':classlist})

def addclass(request):
    if request.method == 'GET':
        return render(request,'addclass.html')

    else:
        # pass
        v = request.POST.get('title')
        print(v)
        # 创建连接
        conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='t1', charset='utf8')
        # 创建游标
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

        # 执行SQL，并返回收影响行数
        cursor.execute("insert into class(title) values(%s) ",(v))

        conn.commit()
        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()
        return redirect('/classes/')



def del_class(request):
    nid = request.GET.get('nid')

    print(nid,type(nid))

    # 创建连接
    conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='t1', charset='utf8')
    # 创建游标
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

    # 执行SQL，并返回收影响行数
    cursor.execute("delete from class where id=%s ", (nid))

    conn.commit()
    # 关闭游标
    cursor.close()
    # 关闭连接
    conn.close()
    return redirect('/classes/')

def updata_class(request):
    if request.method == 'GET':
        nid = request.GET.get('nid')

        print(nid, type(nid))

        # 创建连接
        conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='t1', charset='utf8')
        # 创建游标
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

        # 执行SQL，并返回收影响行数
        cursor.execute("select id,title from class where id=%s ", (nid,))

        result = cursor.fetchone()
        # conn.commit()
        # 关闭游标
        # print(result)
        cursor.close()
        # 关闭连接
        conn.close()
        return render(request,'updata_class.html',{'result':result})

    else:
        nid = request.GET.get('nid')
        title = request.POST.get('classes')

        print('nid',nid,title)

        # 创建连接
        conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='t1', charset='utf8')
        # 创建游标
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

        # 执行SQL，并返回收影响行数
        res = "update class set title = {} where id = {}".format(title,nid)
        print('res:',res)
        cursor.execute("""update class set title = "{}" where id = {}""".format(title,nid))

        # result = cursor.fetchone()
        conn.commit()
        # 关闭游标
        # print(result)
        cursor.close()
        # 关闭连接
        conn.close()
        return redirect('/classes/')


def students(request):


    # 创建连接
    conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='t1', charset='utf8')
    # 创建游标
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

    # 执行SQL，并返回收影响行数
    cursor.execute("SELECT students.id,students.`name`,class.title from students LEFT JOIN class on students.student_class = class.id")

    result = cursor.fetchall()
    # conn.commit()
    # 关闭游标
    print(result)
    cursor.close()
    # 关闭连接
    conn.close()
    # return render(request, 'updata_class.html', {'result': result})



    return render(request,'students.html',{'result': result})


def add_students(request):
    if request.method == 'GET':
        # 创建连接
        conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='t1', charset='utf8')
        # 创建游标
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

        # 执行SQL，并返回收影响行数
        cursor.execute("""select class.title,class.id from class """)

        result = cursor.fetchall()

        print("result.title",result)
        conn.commit()
        # 关闭游标
        # print(result)
        cursor.close()
        # 关闭连接
        conn.close()
        # return redirect('/classes/')

        return render(request,'add_students.html/',{'result':result})
    else:

        name  = request.POST.get('name')
        classes_id  = request.POST.get('class_id')

        conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='t1', charset='utf8')
        # 创建游标
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        print(name,classes_id)
        # 执行SQL，并返回收影响行数
        cursor.execute("""insert into students(`name`,student_class) values (%s,%s)""",[name,classes_id,])

        # result = cursor.fetchall()

        # print("result.title",result)
        conn.commit()
        # 关闭游标
        # print(result)
        cursor.close()
        # 关闭连接
        conn.close()
        return redirect('/students/')

#接口地址
def jiekou(request):
    if request.method == 'GET':
        # 创建连接
        conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='t1', charset='utf8')
        # 创建游标
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

        # 执行SQL，并返回收影响行数
        cursor.execute("""select class.title,class.id from class """)

        result = cursor.fetchall()

        print("result.title",result)
        conn.commit()
        # 关闭游标
        # print(result)
        cursor.close()
        # 关闭连接
        conn.close()
        # return redirect('/classes/')

        return HttpResponse(result)

from utils import sqlhelper
def edic_student(request):
    if request.method == 'GET':
        nid = request.GET.get('nid')
        class_list = sqlhelper.get_list('select class.title,class.id from class ',[])
        student = sqlhelper.get_one('select * from students where  id=%s',[nid,])
        print(student)
        # print(student.name)
        # print(student.id)

        # print(nid)
        return  render(request,'edit__students.html/',{'result':class_list,'student':student})

    else:

        username = request.POST.get('name')
        class_id = request.POST.get('class_id')
        id = request.POST.get('id')
        print('edic_student',class_id)
        print('edic_student', username)
        print('id', id)
        sqlhelper.modify("""update students set student_class = %s ,`name` = %s where id = %s""",[class_id,username,id,])
        return redirect('/students/')


